package com.kcs.dc.report.model.logic;

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import com.kcs.common.framework.GenericDto;
import com.kcs.common.utils.ConnectionUtils;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.ConstantMessage;
import com.kcs.common.utils.LogWriter;
import com.kcs.common.utils.PropsUtils;
import com.kcs.common.utils.commonUtils;
import com.kcs.dc.report.model.dao.reportDAO;
import com.kcs.dc.report.model.dao.model.ReportTypeModel;
import com.kcs.dc.report.model.dao.model.utilGridInfoModel;
import com.kcs.dc.report.view.dto.MainMenuDto;
import com.kcs.dc.report.view.dto.ReportSearchDto;

public class reportBL {

	private reportDAO dao = null;
	private Connection conn = null;
	private String REPORT_LOG_DEALCENTER = "SFAIL_LOG";
	
	public reportBL() {
		dao = new reportDAO();
	}
	
	public GenericDto initEvent(ReportSearchDto dto) throws Exception{
		
		LogWriter.debug("<=====Start int(ReportSearch) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		try{
			LogWriter.debug("Step 1 : Check Authorized to use page.", Constant.LOGGER_APPLICATION);
			
			if(!commonBL.checkAuthenUsePage(dto.getUserId(), conn, "config.rpt")){
				
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_NOHAVE_AUTHEN_PAGE, null);	        	
	        	dto.setService_id("welcome");
	        	
	        	// have error
				MainMenuDto menuDto = new MainMenuDto();
				menuDto.populate(dto);
				return menuDto;	
	        	
			}else{
				// Get ReportType
				LogWriter.debug("Step 2 : Get reportType show on combo.", Constant.LOGGER_APPLICATION);
				getMenuReportType(dto, conn);
				dto.setService_id("report");
			}
	
		}catch(Exception e){
			LogWriter.error("reportBL [Method : initEvent] : " + e, Constant.LOGGER_APPLICATION);			
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
		
		return dto;
	}
		
	private void getMenuReportType(ReportSearchDto dto, Connection conn) throws SQLException{
	
		ReportTypeModel[] model = null;
        
		try{
			model = dao.selReportType(dto.getUserId(), conn,  "config.rpt");
  	
	        if (model != null ) {
	        	String[] rptValue = new String[model.length];
	        	String[] rptDisplay = new String[model.length];
	        	for (int i = 0; i < model.length; i++) {
					rptValue[i] = model[i].getValue();
					rptDisplay[i] = model[i].getDisplay();
				}        	
	        	dto.setReportTypeValue(rptValue);
	        	dto.setReportTypeDisplay(rptDisplay);
	        } 
  
		}catch(SQLException e){
			LogWriter.error("reportBL [Method : getMenuReportType] : " + e, Constant.LOGGER_APPLICATION);
			throw new SQLException();
		}
	}	
	 
	public GenericDto previewEvent(ReportSearchDto dto) throws Exception{
		LogWriter.debug("<=====Start preview(ReportSearch) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);
		
		try{			
			LogWriter.debug("Step 1 : validate date condition", Constant.LOGGER_APPLICATION);	
			if(!validateDateCondition(dto)){
				if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER)){
					getInformation(dto, conn);
				}else{
					getLogDCT(dto, conn);
				}
							
				if (dto.getRow() == 0){
					// Don't have data.
					dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_NOT_FOUND, null);
					if(!dto.getRdateFrom().equals("")) dto.setRdateFrom_Attr(Constant.ERR_ATTR);
					if(!dto.getRdateTo().equals("")) dto.setRdateTo_Attr(Constant.ERR_ATTR);
					if(!dto.getIbcCode().equals("")) dto.setIbcCode_Attr(Constant.ERR_ATTR);
					if(!dto.getCustName().equals("")) dto.setCustName_Attr(Constant.ERR_ATTR);
					if(!dto.getRdealId().equals("")) dto.setRdealId_Attr(Constant.ERR_ATTR);
				}
			}
			
			keepOldValue(dto,conn);
			dto.setService_id("report");
			
		}catch(Exception e){
			LogWriter.error("reportBL [Method : previewEvent] : " + e, Constant.LOGGER_APPLICATION);
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
	
		return dto;	
	}
	
	private boolean validateDateCondition(ReportSearchDto dto){
		boolean isError = false;
		
		if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER)){
			if (dto.getRdateFrom().equals("") || dto.getRdateTo().equals("")){   //Both not input.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INPUT_VALDATE, null);
				dto.setFocus_Attr("rdateFrom");
				dto.setRdateFrom_Attr(Constant.ERR_ATTR);
				dto.setRdateTo_Attr(Constant.ERR_ATTR);
				
				isError = true;		
			}else if(dto.getRdateFrom().equals("") && !dto.getRdateTo().equals("")){  //From not input but To input.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INPUT_VALDATE, null);
				dto.setRdateFrom_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("rdateFrom");
				
				isError = true;
			}else if(!commonUtils.isValidDateFormat(dto.getRdateFrom())){  //From invalid format.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
				dto.setRdateFrom_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("rdateFrom");
				
				isError = true;
			}else if(!commonUtils.isValidDateFormat(dto.getRdateTo())){  //To invalid format.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
				dto.setRdateTo_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("rdateTo");
				
				isError = true;
			}else if(commonUtils.convert_ddmmyyyy2int(dto.getRdateTo()) < commonUtils.convert_ddmmyyyy2int(dto.getRdateFrom())){
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_DATETO_LESSTHAN, null);
				dto.setRdateTo_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("rdateTo");
				
				isError = true;
			}
		}else{
			// Log Date
			if (dto.getLogdateFrom().equals("") || dto.getLogdateTo().equals("")){   //Both not input.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INPUT_LOGDATE, null);
				dto.setFocus_Attr("logdateFrom");
				dto.setLogdateFrom_Attr(Constant.ERR_ATTR);
				dto.setLogdateTo_Attr(Constant.ERR_ATTR);
				
				isError = true;		
			}else if(dto.getLogdateFrom().equals("") && !dto.getLogdateTo().equals("")){  //From not input but To input.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INPUT_LOGDATE, null);
				dto.setLogdateFrom_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("logdateFrom");
				
				isError = true;
			}else if(!commonUtils.isValidDateFormat(dto.getLogdateFrom())){  //From invalid format.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
				dto.setLogdateFrom_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("logdateFrom");
				
				isError = true;
			}else if(!commonUtils.isValidDateFormat(dto.getLogdateTo())){  //To invalid format.
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
				dto.setLogdateTo_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("logdateTo");
				
				isError = true;
			}else if(commonUtils.convert_ddmmyyyy2int(dto.getLogdateTo()) < commonUtils.convert_ddmmyyyy2int(dto.getLogdateFrom())){
				dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_DATETO_LESSTHAN, null);
				dto.setLogdateTo_Attr(Constant.ERR_ATTR);					
				dto.setFocus_Attr("logdateTo");
				
				isError = true;
			}
			if (!isError){
				// Value Date
				if(dto.getLdateFrom().equals("") && !dto.getLdateTo().equals("")){  //From not input but To input.
					dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INPUT_VALDATE, null);
					dto.setLdateFrom_Attr(Constant.ERR_ATTR);					
					dto.setLdateTo_Attr("ldateFrom");
					
					isError = true;
				}else if(!dto.getLdateFrom().equals("") && !commonUtils.isValidDateFormat(dto.getLdateFrom())){  //From invalid format.
					dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
					dto.setLdateFrom_Attr(Constant.ERR_ATTR);					
					dto.setFocus_Attr("ldateFrom");
					
					isError = true;
				}else if(!dto.getLdateTo().equals("") && !commonUtils.isValidDateFormat(dto.getLdateTo())){  //To invalid format.
					dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_INVALID_FORMATE_DATE, null);
					dto.setLdateTo_Attr(Constant.ERR_ATTR);					
					dto.setFocus_Attr("ldateTo");
					
					isError = true;
				}else if(commonUtils.convert_ddmmyyyy2int(dto.getLdateTo()) < commonUtils.convert_ddmmyyyy2int(dto.getLdateFrom())){
					dto.setMessage(Constant.PROP_REPORT, ConstantMessage.ERR_DATETO_LESSTHAN, null);
					dto.setLdateTo_Attr(Constant.ERR_ATTR);					
					dto.setFocus_Attr("ldateTo");
					
					isError = true;
				}
			}
		}
		
		return isError;
	}
	
	private void getInformation(ReportSearchDto dto, Connection conn) throws SQLException{
		
		utilGridInfoModel model = null;
        
		try{
			model = dao.getReportInfomation(dto.getReportType(),
					dto.getRdateFrom(), dto.getRdateTo(), dto.getIbcCode(),
					dto.getCustName(), dto.getRdealId(), dto.getTradName(),
					dto.getPrxGroup(), dto.getPrxName(), dto.getCostcenter(), conn);
  	
	        if (model != null ) { 
	        	dto.setColumnField(model.getColunmName());
	        	dto.setColumn(model.getColumn());
	        	dto.setRow(model.getRow());
	        	dto.setReportInfo(model.getGridInfo());
	        } 
  
		}catch(SQLException e){
			LogWriter.error("previewEvent [Method : getInformation] : " + e, Constant.LOGGER_APPLICATION);
			throw new SQLException();
		}
	}
	
	private void getLogDCT(ReportSearchDto dto, Connection conn) throws SQLException{
		
		utilGridInfoModel model = null;
        
		try{
			model = dao.getLogDealCenter(dto.getReportType(),
							dto.getLogdateFrom(), dto.getLogdateTo(),
							dto.getSystem(), dto.getService(),
							dto.getLdealId(), dto.getSrcRef(), dto.getCostId(),
							dto.getLdateFrom(), dto.getLdateTo(), conn);
					
  	
	        if (model != null ) { 
	        	dto.setColumnField(model.getColunmName());
	        	dto.setColumn(model.getColumn());
	        	dto.setRow(model.getRow());
	        	dto.setReportInfo(model.getGridInfo());
	        } 
  
		}catch(SQLException e){
			LogWriter.error("previewEvent [Method : getLogDCT] : " + e, Constant.LOGGER_APPLICATION);
			throw new SQLException();
		}
	}	
	
	private void keepOldValue(ReportSearchDto dto, Connection conn) throws Exception {
	
			getMenuReportType(dto, conn);
			/*Report DealCenter*/				
			dto.setRdateFrom(dto.getRdateFrom());
			dto.setRdateTo(dto.getRdateTo());
			dto.setIbcCode(dto.getIbcCode());
			dto.setCustName(dto.getCustName());
			dto.setRdealId(dto.getRdealId());
			dto.setTradName(dto.getTradName());
			dto.setPrxGroup(dto.getPrxGroup());
			dto.setPrxName(dto.getPrxName());
			dto.setCostcenter(dto.getCostcenter());
			/*Log Fail DealCenter*/
			dto.setLogdateFrom(dto.getLogdateFrom());
			dto.setLogdateTo(dto.getLogdateTo());
			dto.setLdateFrom(dto.getLdateFrom());
			dto.setLdateTo(dto.getLdateTo());
			dto.setSystem(dto.getSystem());
			dto.setService(dto.getService());
			dto.setLdealId(dto.getLdealId());
			dto.setSrcRef(dto.getSrcRef());
			dto.setCostId(dto.getCostId());
	
	}
	
	public GenericDto exportExcelEvent(ReportSearchDto dto) throws Exception{
		
		LogWriter.info("<=====Start exportExcel(ReportSearch) Event=====>", Constant.LOGGER_APPLICATION);
		
		conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO);	
		try{
			if (dto.getArrayData().length > 0 ) { 
		        dto.setWorkbook(createWorkbook(dto));		        		               	        	
	        } 
	       
			keepOldValue(dto, conn);
	        dto.setService_id("report");
			
		}catch(Exception e){			
			LogWriter.error("userprofileBL [Method : searchEvent] : " + e, Constant.LOGGER_APPLICATION);
		}finally{
			ConnectionUtils.closeConnection(conn);
		}
		return dto;				
	}
	
	public HSSFWorkbook createWorkbook(ReportSearchDto dto) throws Exception {
		
		String reportType = dto.getReportType();
		
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(reportType);

        
        /*
         * Style for Condition.
         */
        HSSFCellStyle condCellStyle = wb.createCellStyle();
        HSSFFont condFont = wb.createFont();
        condFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        condCellStyle.setFont(condFont);
        
        /*Set data for search condition.*/
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        
        cell  = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        cell.setCellValue(new HSSFRichTextString(dto.getReportName()));
                
        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        // Separate report for fail log with success log.
        if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER))
        	cell.setCellValue(new HSSFRichTextString("Date From : " + dto.getRdateFrom() + " - " + dto.getRdateTo()));
        else
        	cell.setCellValue(new HSSFRichTextString("Log Date From : " + dto.getLogdateFrom() + " - " + dto.getLogdateTo()));
        
        row = sheet.createRow(2);
        cell = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        // Separate report for fail log with success log.
        if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER))
        	cell.setCellValue(new HSSFRichTextString("IBC Code  : " + (dto.getIbcCode().equals("")?"-":dto.getIbcCode()) +
            		" , " + "Deal ID : " + (dto.getRdealId().equals("")?"-":dto.getRdealId())));      	
        else
        	cell.setCellValue(new HSSFRichTextString("Value date From  : " + dto.getLdateFrom() + " - " + dto.getLdateTo()));
        
        row = sheet.createRow(3);
        cell = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        // Separate report for fail log with success log.
        if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER))
        	cell.setCellValue(new HSSFRichTextString("Customer Name : " + (dto.getCustName().equals("")?"-":dto.getCustName()) +
            		" , " + "Trader Name : " + (dto.getTradName().equals("")?"-":dto.getTradName())));
        else
        	cell.setCellValue(new HSSFRichTextString("System : " + (dto.getSystem().equals("")?"-":dto.getSystem()) +
            		" , " + "Service : " + (dto.getService().equals("")?"-":dto.getService())));
        
        row = sheet.createRow(4);
        cell = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        // Separate report for fail log with success log.
        if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER))        	
        	cell.setCellValue(new HSSFRichTextString("Proxy Group : " + (dto.getPrxGroup().equals("")?"-":dto.getPrxGroup()) + 
        		" , " + "Proxy Name : " + (dto.getPrxName().equals("")?"-":dto.getPrxName())));
        else
        	cell.setCellValue(new HSSFRichTextString("Deal ID : " + (dto.getLdealId().equals("")?"-":dto.getLdealId()) + 
            		" , " + "Source Ref. : " + (dto.getSrcRef().equals("")?"-":dto.getSrcRef())));
        
        
        row = sheet.createRow(5);
        cell = row.createCell(0);
        cell.setCellStyle(condCellStyle);
        // Separate report for fail log with success log.
        if (!dto.getReportType().equals(REPORT_LOG_DEALCENTER))         
        	cell.setCellValue(new HSSFRichTextString("Settlement Cost Center : " + (dto.getCostcenter().equals("")?"-":dto.getCostcenter())));
        else
        	cell.setCellValue(new HSSFRichTextString("Cost Center ID : " + (dto.getCostId().equals("")?"-":dto.getCostId())));
        /*
         * Style for the header cells.
         */
        HSSFCellStyle headerCellStyle = wb.createCellStyle();
        HSSFFont boldFont = wb.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerCellStyle.setFont(boldFont);
        headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);   
        headerCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
        
        /*Set headerName and width*/    	
	 	String[] attrColumn = PropsUtils.getMessage(Constant.PROP_OTHER, reportType).split(","); 
	   	String colScreen = "";
	   	String width = ""; 	
	   	
	   	// Fix on Row[6] for headerName	   
	   	row = sheet.createRow(7);
	   	for(int c = 0; c < attrColumn.length; c++){
			colScreen = attrColumn[c].split(":")[1].replaceAll("<br>", "");
			width = attrColumn[c].split(":")[2].split("px")[0];
			
			// set width (width*10)
			sheet.setColumnWidth(c, (Integer.parseInt(width) * 50));
			        	        
			// set headername
	        cell  = row.createCell(c);
	        cell.setCellStyle(headerCellStyle);
	        cell.setCellValue(new HSSFRichTextString(colScreen));
	        
	   	}         

        /*
         * Set value by each row.
         * Start row[8]
         */
	   	String prefix_excel = PropsUtils.getMessage(Constant.PROP_OTHER, "rpt.prefix.excel"); 
        for (int r = 0; r < dto.getArrayData().length; r++) {
        	 
        	String[] dataCol = dto.getArrayData()[r].split(prefix_excel);
        	row = sheet.createRow(r+8);
        	
        	for(int c = 0; c < attrColumn.length; c++){        		
        		cell = row.createCell(c);
        		HSSFRichTextString data = new HSSFRichTextString(dataCol[c]);
                cell.setCellValue(data);
        		
        	}   
        }
        return wb;
    }
	

}
